/*
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.    
*/


/* Name
' IDBSourceConnection
' Matthew Tuffin 23/08/2007
' Desc
 * Inteface class for all other Database providers
'
' Requirements:
 * System.Data
 * System.Reflection
 * System.Collections
 * System.Windows.Forms
 * Logger
'
' Usage:
 * Objects can not be made of this class. Create a class that inherits this one and make sure it includes the required 
' abstract functions
'
' Limitations:
 * Needs a transaction log for all updates, deletes and insert. When erroring write off all the current tables
'
*/

using System;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Windows.Forms;
using System.Reflection;
using System.Collections.Specialized;
using System.Collections.Generic;
using System.Collections;
using Logger;


namespace DBConnection
{
    /// <summary>
    /// Abstract Class IDBSourceConnection
    /// </summary>
    public abstract class IDBSourceConnection
    {
        /// <summary>
        /// Defines the maximum items allowed in a the command queue
        /// </summary>
        public static readonly int iMaxCommandsOnQueue = 100000;
        /// <summary>
        /// Delegate for connect disconnect function
        /// </summary>
        public delegate void RunConnectionData();
        /// <summary>
        /// Construction Builder
        /// </summary>
        protected DbConnectionStringBuilder m_DBConnStringBuilder;
        /// <summary>
        /// Connection
        /// </summary>
        protected DbConnection m_DBConnection;
        /// <summary>
        /// Command Builder
        /// </summary>
        protected DbCommandBuilder m_DBCommandBuilder;            
        /// <summary>
        /// List Permissions
        /// </summary>
        protected List<DBDataPermission> m_ListPermissions = new List<DBDataPermission>();
        /// <summary>
        /// Dictionary of all the created tableadapters
        /// </summary>
        protected Dictionary<string, DbDataAdapter> m_DictionaryDataAdapters = new Dictionary<string, DbDataAdapter>();
        /// <summary>
        /// Queue of all commands
        /// </summary>
        protected Queue<DbCommand> m_QueueCommands = new Queue<DbCommand>(iMaxCommandsOnQueue);
        /// <summary>
        /// Table of all the table names in the database
        /// </summary>
        protected DataTable m_dbTableNames;
        /// <summary>
        /// List of all the Table Columns in the database
        /// </summary>
        protected DataSet m_dbTables = new DataSet("TableColumns");
        /// <summary>
        /// Main Connection string
        /// </summary>
        protected string m_strlocalConnString = "";
        /// <summary>
        /// List of Async Command Processes
        /// </summary>
        protected List<IAsyncResult> m_ListASyncResult = new List<IAsyncResult>();

        /// <summary>
        /// To determine if a connection has been disabled since it failed the first connection test
        /// </summary>
        protected bool bConnectionDisabled = false;

        /// <summary>
        /// Abstract function defining what a Provider should do with an Exception
        /// </summary>
        /// <param name="ex">Exception</param>
        public abstract void SQLExceptionMessageBox(DbException ex);

        /// <summary>
        /// Abstract function for defining an update on an adapter
        /// </summary>
        /// <param name="strQuery">Query</param>
        /// <param name="strAdapterName">Adpater Name</param>
        /// <returns>DataTable</returns>
        public abstract DataTable RequestDataTableUsingSQLAdapter(string strQuery, string strAdapterName);


        /// <summary>
        /// Get the main connection associated with this class
        /// </summary>
        public DbConnection MainConnection
        {
            get
            {
                return m_DBConnection;
            }
            set
            {
                m_DBConnection = value;
            }

        }

        /// <summary>
        /// Get the main connection string associated with this class
        /// </summary>
        public string LocalConnString
        {
            get
            {
                return m_strlocalConnString;
            }
            set
            {
                m_strlocalConnString = value;
            }
            
        }

        /// <summary>
        /// Default Constructor always gets called and sets up the location of the Error Log
        /// </summary>
        public IDBSourceConnection()
        {
            SetLogPaths();
        }

        /// <summary>
        /// Constructor that accepts a connectiong string in
        /// </summary>
        /// <param name="strConnString">Connection String</param>
        public IDBSourceConnection(string strConnString)
        {
            m_strlocalConnString = strConnString;
            SetLogPaths();
        }

        /// <summary>
        /// Set the Path to the log files
        /// </summary>
        public void SetLogPaths()
        {
            try
            {
                if (System.IO.Directory.Exists(System.IO.Directory.GetCurrentDirectory() + "\\Log") == false)
                {
                    System.IO.Directory.CreateDirectory(System.IO.Directory.GetCurrentDirectory() + "\\Log");
                    System.IO.Directory.CreateDirectory(System.IO.Directory.GetCurrentDirectory() + "\\TableDumps");
                }
                ErrorLog.LogFilePath = System.IO.Directory.GetCurrentDirectory() + "\\Log\\Error.log";
                ErrorLog.ResetLogFile();

                TransactionLog.LogFilePath = System.IO.Directory.GetCurrentDirectory() + "\\Log\\Transaction.log";
                TransactionLog.ResetLogFile();
            }
            catch (Exception)
            {
                if (System.IO.Directory.Exists("C:\\DBLog\\Log") == false)
                {
                    System.IO.Directory.CreateDirectory("C:\\DBLog" + "\\Log");
                    System.IO.Directory.CreateDirectory("C:\\DBLog" + "\\TableDumps");
                }
                ErrorLog.LogFilePath = "C:\\DBLog" + "\\Log\\Error.log";
                TransactionLog.LogFilePath = "C:\\DBLog" + "\\Log\\Transaction.log";
            }
        }

        /// <summary>
        /// Creates a Connection Object. Passing in the Type which must be derived from DbConnection and a connection string
        /// </summary>
        /// <param name="dbType">Type derived from DbConnectiobn</param>
        /// <param name="strConnString">The Connection String to use</param>
        protected void SetConnection(Type dbType, string strConnString)
        {
            ConstructorInfo constructorInfoObj = dbType.GetConstructor(new Type[] { typeof(string) });
            m_DBConnection = (DbConnection)constructorInfoObj.Invoke(new object[] { strConnString });
            if (TestConnectionString() == false)
            {
                CMsgBox.ShowMsgBox("Connection String Failed to connect");                
                return;
            }         
        }

        /// <summary>
        /// Creates a ConnectionBuilder. Passing in the Type that must be derived from dbConnectionStringBuilder
        /// </summary>
        /// <param name="ConBuilderType">Type derived from dbConnectionStringBuilder</param>
        protected void SetConnBuilder(Type ConBuilderType)
        {
            ConstructorInfo constructorInfoObj = ConBuilderType.GetConstructor(new Type[] { typeof(string) });
            m_DBConnStringBuilder = (DbConnectionStringBuilder)constructorInfoObj.Invoke(new object[] { m_strlocalConnString });   
        }

        /// <summary>
        /// Creates an Database Adapter. Passing in the Type and the name of the new Adapter
        /// </summary>
        /// <param name="AdapterType">Type derived from DbDataAdapter</param>
        /// <param name="strName">The Name of the Adapter usually a query name</param>
        protected void SetAdapter(Type AdapterType, string strName)
        {
            DbDataAdapter DataAdapter;
            ConstructorInfo constructorInfoObj = AdapterType.GetConstructor(new Type[] { });
            DataAdapter = (DbDataAdapter)constructorInfoObj.Invoke(new object[] { });
            m_DictionaryDataAdapters.Add(strName, DataAdapter);
        }

        /// <summary>
        /// Creates a CommandBuilder. Passing in the Type derived from DbCommandBuilder
        /// </summary>
        /// <param name="CommandBuilderType">Type derived from DbCommandBuilder</param>
        protected void SetCommandBuilder(Type CommandBuilderType)
        {
            ConstructorInfo constructorInfoObj = CommandBuilderType.GetConstructor(new Type[] { });
            m_DBCommandBuilder = (DbCommandBuilder)constructorInfoObj.Invoke(new object[] { });               
        }

        /// <summary>
        /// Creates New Parameter. Passing in the Type derived from IParameter
        /// </summary>
        /// <param name="ParameterType">Type derived from IParameter</param>
        /// <returns>returns the Parameter object</returns>
        protected object CreateNewParameter(Type ParameterType)
        {
            ConstructorInfo constructorInfoObj = ParameterType.GetConstructor(new Type[] {  });
            return(constructorInfoObj.Invoke(new object[] { }));
        }

        #region Permissions
        /// <summary>
        /// Adds a permission to a Database Connection
        /// </summary>
        /// <param name="strConnString">Connection String</param>
        /// <param name="strRestrictions">The Restriction imposed</param>
        /// <param name="Behaviour">Enumerator of KeyRestrictionBehavior type AllowedOnly or PreventUsage</param>
        /// <param name="PermissionType">Permission Type derived from DBDataPermission</param>
        /// <returns>Returns the base class DBDataPermission</returns>
        protected virtual DBDataPermission AddPermission(string strConnString, string strRestrictions, System.Data.KeyRestrictionBehavior Behaviour, Type PermissionType)
        {
            DBDataPermission Permission;

            ConstructorInfo constructorInfoObj = PermissionType.GetConstructor(new Type[] { typeof(System.Security.Permissions.PermissionState) });

            Permission = (DBDataPermission)constructorInfoObj.Invoke(new object[] { System.Security.Permissions.PermissionState.None });

            Permission.Add(strConnString, strRestrictions, Behaviour);
            if (Behaviour == System.Data.KeyRestrictionBehavior.AllowOnly)
            {
                Permission.PermitOnly();
            }
            else
            {
                Permission.Deny();
            }

            return Permission;
        }

        /// <summary>
        /// Delete All the permissions
        /// </summary>
        public virtual void FlushPermissions()
        {
            m_ListPermissions.Clear();
        }
        #endregion

        #region ConnectDisconnect

        /// <summary>
        /// Connect on the current setup connection string
        /// </summary>
        /// <returns>True if successful</returns>
        public virtual bool Connect()
        {
            if (bConnectionDisabled == false)
            {
                try
                {
                    m_DBConnection.Open();
                }
                catch (Exception ex)
                {
                    ErrorLog.HandleException(ex);
                    return false;
                }
                return true;
            }
            else
            {
                CMsgBox.ShowMsgBox("Connection has been disabled please re-run the TestConnection to correct this");
                return false;
            }
        }

        /// <summary>
        /// Disconnects on the current setup connection string
        /// </summary>
        /// <returns>True if successful</returns>
        public virtual bool Disconnect()
        {
            if (bConnectionDisabled == false)
            {
                try
                {
                    m_DBConnection.Close();
                }
                catch (Exception ex)
                {
                    ErrorLog.HandleException(ex);
                    return false;
                }

                return true;
            }
            else
            {
                CMsgBox.ShowMsgBox("Connection has been disabled please re-run the TestConnection to correct this");
                return false;
            }
        }

        /// <summary>
        /// Connects then runs the function pointer that was passed to it and then disconnects
        /// </summary>
        /// <param name="DataRun">Function pointer to "void Function()"</param>
        public virtual bool CommunicateToServer(RunConnectionData DataRun)
        {
            if (bConnectionDisabled == false)
            {
                try
                {
                    m_DBConnection.Open();
                    DataRun();
                    m_DBConnection.Close();
                    return true;
                }
                catch (System.Data.Common.DbException dbex)
                {
                    CMsgBox.ShowMsgBox(dbex.Message);
                    SQLExceptionMessageBox(dbex);
                    return false;
                }
                catch (Exception ex)
                {
                    CMsgBox.ShowMsgBox(ex.Message);
                    ErrorLog.HandleException(ex);
                    return false;
                }
            }
            else
            {
                CMsgBox.ShowMsgBox("Connection has been disabled please re-run the TestConnection to correct this");
                return false;
            }
        }
        #endregion

        #region CommandQueue
            /// <summary>
        /// Add Query to a Command Object and then to the end of the Command Queue
        /// </summary>
        /// <param name="strSQL">string with Query</param>
        public virtual void AddCommandToQueue(string strSQL)
        {
            AddCommandToQueue(ConstructSQLCommand(strSQL));
        }

        /// <summary>
        /// Add Query to a Command Object and then to the end of the Command Queue and Execute it
        /// </summary>
        /// <param name="strSQL">string with Query</param>
        public virtual void AddCommandToQueueAndExectue(string strSQL)
        {
            AddCommandToQueue(ConstructSQLCommand(strSQL));
            ConnectExectueAndFlushQueue();
        }

       
        private void ConnectExectueAndFlushQueue()
        {
            Connect();
            ExecuteCommandQueue();
            Disconnect();
            FlushCommandQueue();
        }

        /// <summary>
        /// Add Command object to the end of the Queue and Execute it
        /// </summary>
        /// <param name="Command">Command Object</param>
        /// <returns>True if successful</returns>
        public virtual void AddCommandToQueueAndExectue(DbCommand Command)
        {
            AddCommandToQueue(Command);
            ConnectExectueAndFlushQueue();
        }


        /// <summary>
        /// Add Command object to the end of the Queue
        /// </summary>
        /// <param name="Command">Command Object</param>
        /// <returns>True if successful</returns>
        public virtual bool AddCommandToQueue(DbCommand Command)
        {
            try
            {
                m_QueueCommands.Enqueue(Command);
            }
            catch (Exception ex)
            {
                CMsgBox.ShowMsgBox(ex.Message);
                ErrorLog.HandleException(ex);
                return false;
            }
            return true;
        }

        /// <summary>
        /// Executes the currect command queue. If an error is encoutered with any item in the Queue.
        /// Queue will rollback all actions it has taken.
        /// </summary>
        public virtual void ExecuteCommandQueue()
        {
            int iRowsAffected = 0;
            using(DbTransaction Transaction = m_DBConnection.BeginTransaction())
            {
                try
                {
                    foreach (DbCommand Command in m_QueueCommands)
                    {
                        //Some way to track the return value of this......                
                        Command.Transaction = Transaction;
                        iRowsAffected = Command.ExecuteNonQuery();                       
                        TransactionLog.HandleTransaction("Transaction using query: " + Command.CommandText +
                                  " Number of rows affected: " + iRowsAffected.ToString());

                    }
                    Transaction.Commit();
                }
                catch (System.Data.Common.DbException dbex)
                {
                    SQLExceptionMessageBox(dbex);
                    CMsgBox.ShowMsgBox("Rollback Triggered");
                    Transaction.Rollback();
                }
                catch (Exception ex)
                {
                    ErrorLog.HandleException(ex);                    
                }
            }
        }

        /// <summary>
        /// Executes the command queue properly so that it has a connect and disconnect on it
        /// </summary>
        public virtual void ConnectandExectureCommandQueue()
        {
            CommunicateToServer(new IDBSourceConnection.RunConnectionData(ExecuteCommandQueue));
        }

        /// <summary>
        /// Flushes out the command queue
        /// </summary>
        public virtual void FlushCommandQueue()
        {
            m_QueueCommands.Clear();
        }

        #endregion

        #region DiscoverSchema
        /// <summary>
        /// Gets Schema information about tables ONLY WORKS FOR SQL_SERVER SO FAR
        /// </summary>
        protected void SetSchemaTables()
        {
            m_dbTableNames = m_DBConnection.GetSchema("Tables");
            BuildDataSetofTableColumns();
        }

        /// <summary>
        /// Builds a table of all the columns for each table
        /// </summary>
        protected void BuildDataSetofTableColumns()
        {
            foreach (DataRow row in m_dbTableNames.Rows)
            {
                m_dbTables.Tables.Add(DiscoverTableColumns(row["TABLE_NAME"].ToString(), row["TABLE_CATALOG"].ToString()));
            }
        }

        /// <summary>
        /// Given a tablename and the database constructs datatable containing the column names of that table
        /// </summary>
        /// <param name="strTableName">Table Name</param>
        /// <param name="strDatabaseName">Database Name</param>
        /// <returns>DataTable contained column names</returns>
        public DataTable DiscoverTableColumns(string strTableName, string strDatabaseName)
        {
            DataTable dbColumns;
            string[] strRestrictions = new string[] { strDatabaseName, "dbo", strTableName, null };
            dbColumns = m_DBConnection.GetSchema("Columns", strRestrictions);
            dbColumns.TableName = strTableName;
            return (dbColumns);
        }

        /// <summary>
        /// Returns a stringcollection of all the columnnames in a particular table
        /// </summary>
        /// <param name="strTableName">Table Name</param>
        /// <returns>StringCollection with column names</returns>
        public StringCollection ObtainFieldNamesForTable(string strTableName)
        {
            StringCollection strFieldNames = new StringCollection();

            foreach (DataRow Row in m_dbTables.Tables[strTableName].Rows)
            {
                strFieldNames.Add(Row["COLUMN_NAME"].ToString());
            }
            return (strFieldNames);
        }

        #endregion

        /// <summary>
        /// Construct a Command Object 
        /// </summary>
        /// <param name="strQuery">String Query</param>
        /// <returns>returns DbCommand</returns>
        public virtual DbCommand ConstructSQLCommand(string strQuery)
        {
            DbCommand Command = m_DBConnection.CreateCommand();
            Command.CommandText = strQuery;            
            return (Command);
        }

        /// <summary>
        /// Create a new stored procedure command
        /// </summary>
        /// <param name="strExec">Stored procedure command</param>
        /// <returns>Command</returns>
        public DbCommand CreateStoredProcedureCommand(string strExec)
        {
            DbCommand Command = ConstructSQLCommand(strExec);
            Command.CommandType = CommandType.StoredProcedure;
            AddCommandToQueue(Command);
            return (Command);
        }

        /// <summary>
        /// Return a datareader for a SELECT query
        /// </summary>
        /// <param name="Query">Command with a Select Query</param>
        /// <returns>DataReader</returns>
        public virtual DbDataReader ReadSQLIntoDataReader(DbCommand Query)
        {   
            return (Query.ExecuteReader(CommandBehavior.CloseConnection));
        }

        /// <summary>
        /// Accepts a string query and returns datareader
        /// </summary>
        /// <param name="strQuery">Select Query</param>
        /// <returns>DataReader</returns>
        public virtual DbDataReader ReadSQLIntoDataReader(string strQuery)
        {
            return (ReadSQLIntoDataReader(ConstructSQLCommand(strQuery)));
        }
        
        /// <summary>
        /// Returns a single value from a column in a database
        /// </summary>
        /// <param name="strQuery">Select Query for one single column</param>
        /// <returns>Data in the column</returns>
        public object ReturnSingleValue(string strQuery)
        {
            return (ConstructSQLCommand(strQuery).ExecuteScalar());
        }

        /// <summary>
        /// Returns a single value from a column in a database connecting and disconnecting from db
        /// </summary>
        /// <param name="strQuery">Select Query for one single column</param>
        /// <returns>Data in the column</returns>
        public object ReturnSingleValueConnectAndDisconnect(string strQuery)
        {
            Connect();
            object oValue = ConstructSQLCommand(strQuery).ExecuteScalar();
            Disconnect();
            return (oValue);
        }
        
        /// <summary>
        /// Returns the schema info for a table from a datareader
        /// </summary>
        /// <param name="Reader">DataReader</param>
        /// <returns>DataTable with Schema info</returns>
        public DataTable ReturnSchemaForActiveReader(DbDataReader Reader)
        {
            return (Reader.GetSchemaTable());
        }

        /// <summary>
        /// Given a query and a name creates a new datatable and adapter for that query
        /// </summary>
        /// <param name="strQuery">Select Query</param>
        /// <param name="strAdapterName">New Name of Adapter</param>
        /// <returns>DataTable</returns>
        protected DataTable SelectSQLIntoDataTable(string strQuery, string strAdapterName)
        {
            return (SelectSQLIntoDataTable(ConstructSQLCommand(strQuery), strAdapterName));
        }

        /// <summary>
        /// Creates a datatable and adapter for a command object
        /// </summary>
        /// <param name="Command">Contains Select Query</param>
        /// <param name="strAdapterName">New Name of Adapter</param>
        /// <returns>DataTable</returns>
        protected DataTable SelectSQLIntoDataTable(DbCommand Command, string strAdapterName)
        {
            try
            {
                if(bConnectionDisabled)
                {
                    throw(new Exception("Connection has been disabled please re-run the TestConnection to correct this"));
                }
                DbDataAdapter dataAdapter = m_DictionaryDataAdapters[strAdapterName];
                dataAdapter.FillError += new FillErrorEventHandler(DefaultFillErrorEvent);
                dataAdapter.SelectCommand = Command;
                DataTable DT = new DataTable(strAdapterName);
                dataAdapter.FillSchema(DT,SchemaType.Source);
                dataAdapter.Fill(DT);
                return DT;
            }
            catch (Exception ex)
            {
                CMsgBox.ShowMsgBox(ex.Message);
                ErrorLog.HandleException(ex);
                return (null);
            }
        }

        /// <summary>
        /// Refresh An Existing Datatable and adapter
        /// </summary>
        /// <param name="DT">DataTable</param>
        /// <param name="strAdapterName">AdapterName</param>
        public DataTable RefreshExistingDataTable(DataTable DT, string strAdapterName)
        {
            DbDataAdapter dataAdapter = m_DictionaryDataAdapters[strAdapterName];
            dataAdapter.FillError += new FillErrorEventHandler(DefaultFillErrorEvent);
            dataAdapter.Fill(DT);
            return DT;
        }

        /// <summary>
        /// Default Event Fired when an adapter failed a filling event
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private static void DefaultFillErrorEvent(object sender, FillErrorEventArgs e)
        {
            CMsgBox.ShowMsgBox("Error Filling a DataTable " + e.Errors.Message);
        }

        /// <summary>
        /// Creates a new table with all the schema information from a particular query NO DATA will return
        /// </summary>
        /// <param name="Command">Contains Query</param>
        /// <param name="strAdapterName">Name of Adapter</param>
        /// <returns>DataTable</returns>
        protected DataTable SelectSQLIntoDataTableSchemaOnly(DbCommand Command, string strAdapterName)
        {
            DbDataAdapter dataAdapter = m_DictionaryDataAdapters[strAdapterName];
            dataAdapter.SelectCommand = Command;            
            DataTable DT = new DataTable(strAdapterName);
            dataAdapter.FillSchema(DT,SchemaType.Source);
            return DT;
        }

        /// <summary>
        /// Forces an adapter to have strict rules about tablemapping into the adapter
        /// </summary>
        /// <param name="strAdapterName">AdapterName</param>
        public void EnableStrictTableMappingsOnAdapter(string strAdapterName)
        {
            m_DictionaryDataAdapters[strAdapterName].MissingMappingAction = MissingMappingAction.Error;
            m_DictionaryDataAdapters[strAdapterName].MissingSchemaAction = MissingSchemaAction.Error;
        }

        /// <summary>
        /// Continues trying to update a table even if an error occurs 
        /// </summary>
        /// <param name="strAdapterName">AdapterName</param>
        public void IgnoreUpdateErrorsOnAdpater(string strAdapterName)
        {
            m_DictionaryDataAdapters[strAdapterName].ContinueUpdateOnError = true;         
        }

        /// <summary>
        /// Get Adapter for TableName
        /// </summary>
        /// <param name="strAdapterName">AdapterName</param>
        public DbDataAdapter GetAdapterForTable(string strAdapterName)
        {
            try
            {
                return (m_DictionaryDataAdapters[strAdapterName]);
            }
            catch (Exception)
            {
                return null;
            }
            
        }


        /// <summary>
        /// Adds an update, delete and insert to table adapter
        /// </summary>
        /// <param name="UpdateCommand">UpdateCommand</param>
        /// <param name="DeleteCommand">DeleteCommand</param>
        /// <param name="InsertCommand">InsertCommand</param>
        /// <param name="strAdapterName">AdapterName</param>
        /// <returns>True if succesful</returns>
        public bool AddUpdateDeleteInsertToAdapter(DbCommand UpdateCommand, DbCommand DeleteCommand,
                                                   DbCommand InsertCommand, string strAdapterName)
        {
            try
            {
                DbDataAdapter dbAdapter = m_DictionaryDataAdapters[strAdapterName];
                dbAdapter.UpdateCommand = UpdateCommand;
                dbAdapter.DeleteCommand = DeleteCommand;
                dbAdapter.InsertCommand = InsertCommand;
                return (true);
            }
            catch (Exception ex)
            {
                CMsgBox.ShowMsgBox(ex.Message);
                ErrorLog.HandleException(ex);
                return (false);
            }
        }

        /// <summary>
        /// Gets all parameters on used on a select command on a table adpater
        /// </summary>
        /// <param name="strAdapterName">AdapterName</param>
        /// <returns>List of Parameters</returns>
        public IDataParameter[] GetSelectParametersOnAdapter(string strAdapterName)
        {
            return(m_DictionaryDataAdapters[strAdapterName].GetFillParameters());
        }

        /// <summary>
        /// Removes a table adapter from the dictionary
        /// </summary>
        /// <param name="strAdapterName">AdapterName</param>
        public void ClearTableAdapter(string strAdapterName)
        {
            try
            {
                m_DictionaryDataAdapters.Remove(strAdapterName);
            }
            catch(Exception ex)
            {
                CMsgBox.ShowMsgBox(ex.Message);
                ErrorLog.HandleException(ex);
            }
        }

        /// <summary>
        /// Generates Table Mappings for an adapter. Note:
        /// Both string tables must have the same columns and be in the order you wish to add them in.
        /// </summary>
        /// <param name="strTableName">TableName</param>
        /// <param name="strNewTableName">NewTableName</param>
        /// <param name="strColumnName">Collection of original ColumnName</param>
        /// <param name="strNewColumnName">Collection of new ColumnName</param>
        /// <returns>DataTableMapping Collection</returns>
        public DataTableMapping GenerateCollectionofTableMappings(string strTableName, string strNewTableName,
                                                      string[] strColumnName, string[] strNewColumnName)
        {
            DataTableMapping TableMapping = new DataTableMapping(strTableName,strNewTableName);
            for (int i = 0; i < strColumnName.Length; ++i)
            {
                TableMapping.ColumnMappings.Add(strColumnName[i], strNewColumnName[i]);
            }

            return (TableMapping);
        }

        /// <summary>
        /// Adds a TableMapping to an Adapter
        /// </summary>
        /// <param name="strAdapter">AdapterName</param>
        /// <param name="TableMap">DataTableMapping Collection</param>
        public void AddTableMapToDataAdapter(string strAdapter, DataTableMapping TableMap)
        {
            m_DictionaryDataAdapters[strAdapter].TableMappings.Add(TableMap);
        }

        /// <summary>
        /// Changes the Adapter's Batch Size
        /// </summary>
        /// <param name="iBatchSize">New Batch Size</param>
        /// <param name="strAdapterName">AdapterName</param>
        public void SetAdapterBatchSize(int iBatchSize, string strAdapterName)
        {
            m_DictionaryDataAdapters[strAdapterName].UpdateBatchSize = iBatchSize;
        }

        /// <summary>
        /// Auto Generates an update, delete and insert statement from an adapter with a select statement
        /// </summary>
        /// <param name="strAdapterName">AdapterName</param>
        /// <returns>True if succesful</returns>
        public bool AutoGenUpdateDeleteInsert(string strAdapterName)
        {
            try
            {
                if (bConnectionDisabled)
                {
                    throw(new Exception("Connection has been disabled please re-run the TestConnection to correct this"));
                }
                DbDataAdapter Adapter = m_DictionaryDataAdapters[strAdapterName];
                m_DBCommandBuilder.DataAdapter = Adapter;
                Adapter.UpdateCommand = ConstructSQLCommand(m_DBCommandBuilder.GetUpdateCommand(true).CommandText);
                Adapter.InsertCommand = ConstructSQLCommand(m_DBCommandBuilder.GetInsertCommand(true).CommandText);
                Adapter.DeleteCommand = ConstructSQLCommand(m_DBCommandBuilder.GetDeleteCommand(true).CommandText); 
                m_DBCommandBuilder.DataAdapter = null;
                return (true);
            }
            catch (Exception ex)
            {
                CMsgBox.ShowMsgBox(ex.Message);
                ErrorLog.HandleException(ex);
                return (false);
            }
        }

      

        /// <summary>
        /// Tests a ConnectionString
        /// </summary>
        /// <returns>True if connection was successful</returns>
        private bool TestConnectionString()
        {
            bConnectionDisabled = false;
            if (Connect())
            {
                Disconnect();               
                return true;
            }
            bConnectionDisabled = true;
            return false;
        }

        #region UpdatingDatabase

        /// <summary>
        /// Updates a database table using an Adpater with a Dataset
        /// </summary>
        /// <param name="strAdapterName">AdapterName</param>
        /// <param name="DS">DataSet</param>
        /// <param name="strTableName">Table Name from DataSet</param>
        /// <returns>RowsAffected</returns>
        public int UpdateDataInAdapter(string strAdapterName, DataSet DS, string strTableName)
        {
            int iRowsAffected = 0;
           
                Connect();
                using (DbTransaction Transaction = m_DBConnection.BeginTransaction())
                { 
                    try
                    {
                        DbDataAdapter Adapter = m_DictionaryDataAdapters[strAdapterName];
                        if ((Adapter.UpdateCommand != null)
                        && (Adapter.DeleteCommand != null)
                        && (Adapter.InsertCommand != null))
                        {
                            Adapter.UpdateCommand.Transaction = Transaction;
                            Adapter.InsertCommand.Transaction = Transaction;
                            Adapter.DeleteCommand.Transaction = Transaction;

                            iRowsAffected = Adapter.Update(DS, strTableName);
                            Transaction.Commit();
                            Disconnect();

                            TransactionLog.HandleTransaction("Transaction on: " + DS.Tables[strTableName].TableName +
                                                          "\n SelectQuery: " + m_DictionaryDataAdapters[strAdapterName].SelectCommand.CommandText +
                                                          "\n UpdateQuery: " + m_DictionaryDataAdapters[strAdapterName].UpdateCommand.CommandText +
                                                          "\n InsertQuery: " + m_DictionaryDataAdapters[strAdapterName].InsertCommand.CommandText +
                                                          "\n DeleteQuery: " + m_DictionaryDataAdapters[strAdapterName].DeleteCommand.CommandText +  
                                                          "\n Number of rows affected: " + iRowsAffected.ToString());                           
                        }
                        else
                        {
                            throw (new Exception("Update, Insert and Delete Commands not specified on this adapter"));
                        }
                    }
                    catch (Exception ex)
                    {
                        CMsgBox.ShowMsgBox(ex.Message);
                        ErrorLog.HandleException(ex);
                        //Write off table data
                        DataTable DT = DS.Tables[strTableName];
                        DataHandling.WriteRecoveryTable(ref DT, strTableName);
                        Transaction.Rollback();
                        throw new Exception(ex.Message);
                    }
                }
            return (iRowsAffected);
        }

        /// <summary>
        /// Updates a database table using an Adpater with a DataTable
        /// </summary>
        /// <param name="strAdapterName">AdapterName</param>
        /// <param name="DT">DataTable</param>
        /// <returns>RowsAffected</returns>
        public int UpdateDataInAdapter(string strAdapterName, DataTable DT)
        {
            int iRowsAffected = 0;
            
                Connect();
           
                using (DbTransaction Transaction = m_DBConnection.BeginTransaction())
                {
                    try
                    {
                        DbDataAdapter Adapter = m_DictionaryDataAdapters[strAdapterName];
                        if ((Adapter.UpdateCommand != null)
                        && (Adapter.DeleteCommand != null)
                        && (Adapter.InsertCommand != null))
                        {
                            Adapter.UpdateCommand.Transaction = Transaction;
                            Adapter.InsertCommand.Transaction = Transaction;
                            Adapter.DeleteCommand.Transaction = Transaction;

                            iRowsAffected = Adapter.Update(DT);
                            //Add Transaction log here
                            Transaction.Commit();
                            Disconnect();
                            TransactionLog.HandleTransaction("Transaction on: " + DT.TableName +
                                                          "\n SelectQuery: " + m_DictionaryDataAdapters[strAdapterName].SelectCommand.CommandText +
                                                          "\n UpdateQuery: " + m_DictionaryDataAdapters[strAdapterName].UpdateCommand.CommandText +
                                                          "\n InsertQuery: " + m_DictionaryDataAdapters[strAdapterName].InsertCommand.CommandText +
                                                          "\n DeleteQuery: " + m_DictionaryDataAdapters[strAdapterName].DeleteCommand.CommandText +
                                                          "\n Number of rows affected: " + iRowsAffected.ToString());                            
                        }
                        else
                        {
                            throw (new Exception("Update, Insert and Delete Commands not specified on this adapter"));
                        }
                    }
                    catch (Exception ex)
                    {
                        CMsgBox.ShowMsgBox(ex.Message);
                        ErrorLog.HandleException(ex);
                        //Write off table data
                        DataHandling.WriteRecoveryTable(ref DT, DT.TableName);
                        Transaction.Rollback();
                    }
                }
            return (iRowsAffected);
        }
        #endregion


        /// <summary>
        /// Returns an insert command that was specified on a dataadpater
        /// </summary>
        /// <param name="strAdapterName">The adapters name which happens to be the queryname</param>
        /// <returns>The Command object representing the insert command</returns>
        public DbCommand GetInsertCommandForGivenTableName(string strAdapterName)
        {
            DbDataAdapter dataAdapter = m_DictionaryDataAdapters[strAdapterName];

            return (dataAdapter.InsertCommand);
        }

        /// <summary>
        /// Returns an update command that was specified on a dataadpater
        /// </summary>
        /// <param name="strAdapterName">The adapters name which happens to be the queryname</param>
        /// <returns>The Command object representing the update command</returns>
        public DbCommand GetUpdateCommandForGivenTableName(string strAdapterName)
        {
            DbDataAdapter dataAdapter = m_DictionaryDataAdapters[strAdapterName];

            return (dataAdapter.UpdateCommand);
        }


        /// <summary>
        /// Returns a dataadpater
        /// </summary>
        /// <param name="strAdapterName">The adapters name which happens to be the queryname</param>
        /// <returns>The dataadpater</returns>
        public DbDataAdapter GetAdapterForGivenTableName(string strAdapterName)
        {
            return (m_DictionaryDataAdapters[strAdapterName]);
        }
    }
}
